{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:51:55.293428Z",
     "iopub.status.busy": "2021-06-14T06:51:55.292408Z",
     "iopub.status.idle": "2021-06-14T06:51:56.545515Z",
     "shell.execute_reply": "2021-06-14T06:51:56.543522Z",
     "shell.execute_reply.started": "2021-06-14T06:51:55.293428Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:51:57.480628Z",
     "iopub.status.busy": "2021-06-14T06:51:57.479627Z",
     "iopub.status.idle": "2021-06-14T06:51:57.498630Z",
     "shell.execute_reply": "2021-06-14T06:51:57.495628Z",
     "shell.execute_reply.started": "2021-06-14T06:51:57.480628Z"
    }
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "from dateutil import parser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:51:58.011673Z",
     "iopub.status.busy": "2021-06-14T06:51:58.010669Z",
     "iopub.status.idle": "2021-06-14T06:51:58.024674Z",
     "shell.execute_reply": "2021-06-14T06:51:58.022671Z",
     "shell.execute_reply.started": "2021-06-14T06:51:58.011673Z"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "from os.path import join "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:51:58.358698Z",
     "iopub.status.busy": "2021-06-14T06:51:58.357712Z",
     "iopub.status.idle": "2021-06-14T06:51:58.374699Z",
     "shell.execute_reply": "2021-06-14T06:51:58.372701Z",
     "shell.execute_reply.started": "2021-06-14T06:51:58.358698Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'D:\\\\Code\\\\PYTHON\\\\notebook\\\\my-test-notebook\\\\运筹学作业\\\\大作业 飞机分配\\\\题目四-航班机型分配\\\\第三问'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cwd = os.getcwd()\n",
    "cwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:51:59.083888Z",
     "iopub.status.busy": "2021-06-14T06:51:59.082888Z",
     "iopub.status.idle": "2021-06-14T06:51:59.139871Z",
     "shell.execute_reply": "2021-06-14T06:51:59.136869Z",
     "shell.execute_reply.started": "2021-06-14T06:51:59.083888Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>dep_ucl</th>\n",
       "      <th>arr_ucl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0001</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021/5/29 7:55</td>\n",
       "      <td>2021/5/29 10:38</td>\n",
       "      <td>2021/5/29 11:55</td>\n",
       "      <td>2021/5/29 17:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0002</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021/5/29 14:05</td>\n",
       "      <td>2021/5/29 21:59</td>\n",
       "      <td>2021/5/29 21:05</td>\n",
       "      <td>2021/5/30 1:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0003</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021/5/29 17:40</td>\n",
       "      <td>2021/5/29 20:25</td>\n",
       "      <td>2021/5/29 21:40</td>\n",
       "      <td>2021/5/30 3:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0004</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021/5/29 8:40</td>\n",
       "      <td>2021/5/29 16:40</td>\n",
       "      <td>2021/5/29 15:40</td>\n",
       "      <td>2021/5/29 20:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0005</td>\n",
       "      <td>EDB</td>\n",
       "      <td>MBY</td>\n",
       "      <td>2021/5/29 9:10</td>\n",
       "      <td>2021/5/29 12:02</td>\n",
       "      <td>2021/5/29 13:10</td>\n",
       "      <td>2021/5/29 19:02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   flight origin destination          deptime          arrtime  \\\n",
       "0  AA0001    EDB         TFB   2021/5/29 7:55  2021/5/29 10:38   \n",
       "1  AA0002    TFB         EDB  2021/5/29 14:05  2021/5/29 21:59   \n",
       "2  AA0003    EDB         TFB  2021/5/29 17:40  2021/5/29 20:25   \n",
       "3  AA0004    TFB         EDB   2021/5/29 8:40  2021/5/29 16:40   \n",
       "4  AA0005    EDB         MBY   2021/5/29 9:10  2021/5/29 12:02   \n",
       "\n",
       "           dep_ucl          arr_ucl  \n",
       "0  2021/5/29 11:55  2021/5/29 17:38  \n",
       "1  2021/5/29 21:05   2021/5/30 1:59  \n",
       "2  2021/5/29 21:40   2021/5/30 3:25  \n",
       "3  2021/5/29 15:40  2021/5/29 20:40  \n",
       "4  2021/5/29 13:10  2021/5/29 19:02  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_sche = pd.read_csv('new_schedual_v2.0.csv')\n",
    "time_sche.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:52:02.403713Z",
     "iopub.status.busy": "2021-06-14T06:52:02.402715Z",
     "iopub.status.idle": "2021-06-14T06:52:03.046767Z",
     "shell.execute_reply": "2021-06-14T06:52:03.043774Z",
     "shell.execute_reply.started": "2021-06-14T06:52:02.403713Z"
    }
   },
   "outputs": [],
   "source": [
    "from collections import defaultdict\n",
    "flight_dict = time_sche.iloc[:,:3].groupby('flight')\n",
    "fli_dict  = defaultdict(dict) \n",
    "for group,values in flight_dict:\n",
    "    values = values.iloc[:,1:]\n",
    "    fli_dict[group] = values.set_index('origin').to_dict()['destination']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:52:05.638247Z",
     "iopub.status.busy": "2021-06-14T06:52:05.637248Z",
     "iopub.status.idle": "2021-06-14T06:52:05.937269Z",
     "shell.execute_reply": "2021-06-14T06:52:05.935268Z",
     "shell.execute_reply.started": "2021-06-14T06:52:05.638247Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>Class</th>\n",
       "      <th>Fare</th>\n",
       "      <th>RD0</th>\n",
       "      <th>RD1</th>\n",
       "      <th>RD2</th>\n",
       "      <th>...</th>\n",
       "      <th>RD89</th>\n",
       "      <th>RD99</th>\n",
       "      <th>RD119</th>\n",
       "      <th>RD139</th>\n",
       "      <th>RD159</th>\n",
       "      <th>RD179</th>\n",
       "      <th>RD209</th>\n",
       "      <th>RD239</th>\n",
       "      <th>RD269</th>\n",
       "      <th>RD330</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>B</td>\n",
       "      <td>230.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>G</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.67</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>H</td>\n",
       "      <td>105.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>K</td>\n",
       "      <td>94.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>L</td>\n",
       "      <td>145.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.67</td>\n",
       "      <td>1.33</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1 Flight2 Flight3 Class   Fare   RD0  RD1  \\\n",
       "0    BER         BOD  AA0040BERBOD       .       .     B  230.0  0.00  0.0   \n",
       "1    BER         BOD  AA0040BERBOD       .       .     G  100.0  0.67  0.0   \n",
       "2    BER         BOD  AA0040BERBOD       .       .     H  105.0  0.00  0.0   \n",
       "3    BER         BOD  AA0040BERBOD       .       .     K   94.0  0.00  0.0   \n",
       "4    BER         BOD  AA0040BERBOD       .       .     L  145.0  0.00  0.0   \n",
       "\n",
       "   RD2  ...  RD89  RD99  RD119  RD139  RD159  RD179  RD209  RD239  RD269  \\\n",
       "0  0.0  ...   0.0  0.00   0.00    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "1  0.0  ...   0.0  0.00   0.00    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "2  0.0  ...   0.0  0.00   0.00    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "3  0.0  ...   0.0  0.00   0.00    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "4  0.0  ...   0.0  0.67   1.33    0.0    0.0    0.0    0.0    0.0    0.0   \n",
       "\n",
       "   RD330  \n",
       "0    0.0  \n",
       "1    0.0  \n",
       "2    0.0  \n",
       "3    0.0  \n",
       "4    0.0  \n",
       "\n",
       "[5 rows x 38 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product = pd.read_csv('data_fam_products.csv')\n",
    "product.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:52:10.902035Z",
     "iopub.status.busy": "2021-06-14T06:52:10.901035Z",
     "iopub.status.idle": "2021-06-14T06:52:10.956039Z",
     "shell.execute_reply": "2021-06-14T06:52:10.953039Z",
     "shell.execute_reply.started": "2021-06-14T06:52:10.902035Z"
    }
   },
   "outputs": [],
   "source": [
    "product['TRD'] = product.loc[:,'RD0':].sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:52:11.453125Z",
     "iopub.status.busy": "2021-06-14T06:52:11.452110Z",
     "iopub.status.idle": "2021-06-14T06:52:11.482111Z",
     "shell.execute_reply": "2021-06-14T06:52:11.480110Z",
     "shell.execute_reply.started": "2021-06-14T06:52:11.453125Z"
    }
   },
   "outputs": [],
   "source": [
    "product.drop(product.columns[5:-1],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:52:11.954169Z",
     "iopub.status.busy": "2021-06-14T06:52:11.954169Z",
     "iopub.status.idle": "2021-06-14T06:52:11.992152Z",
     "shell.execute_reply": "2021-06-14T06:52:11.990167Z",
     "shell.execute_reply.started": "2021-06-14T06:52:11.954169Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>TRD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>2.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>2.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>0.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>13.12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1 Flight2 Flight3    TRD\n",
       "0    BER         BOD  AA0040BERBOD       .       .   2.29\n",
       "1    BER         BOD  AA0040BERBOD       .       .   2.07\n",
       "2    BER         BOD  AA0040BERBOD       .       .   0.66\n",
       "3    BER         BOD  AA0040BERBOD       .       .   0.00\n",
       "4    BER         BOD  AA0040BERBOD       .       .  13.12"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:52:15.633904Z",
     "iopub.status.busy": "2021-06-14T06:52:15.631901Z",
     "iopub.status.idle": "2021-06-14T06:52:15.691905Z",
     "shell.execute_reply": "2021-06-14T06:52:15.689914Z",
     "shell.execute_reply.started": "2021-06-14T06:52:15.632901Z"
    }
   },
   "outputs": [],
   "source": [
    "product = product.groupby(list(product.columns[:5])).TRD.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:52:16.085243Z",
     "iopub.status.busy": "2021-06-14T06:52:16.084243Z",
     "iopub.status.idle": "2021-06-14T06:52:16.105223Z",
     "shell.execute_reply": "2021-06-14T06:52:16.102221Z",
     "shell.execute_reply.started": "2021-06-14T06:52:16.085243Z"
    }
   },
   "outputs": [],
   "source": [
    "product = product.to_frame().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:52:16.525609Z",
     "iopub.status.busy": "2021-06-14T06:52:16.524605Z",
     "iopub.status.idle": "2021-06-14T06:52:16.560608Z",
     "shell.execute_reply": "2021-06-14T06:52:16.559608Z",
     "shell.execute_reply.started": "2021-06-14T06:52:16.525609Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>TRD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>33.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>34.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0106BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>0.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>AA0080BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>2.66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1       Flight2 Flight3    TRD\n",
       "0    BER         BOD  AA0040BERBOD             .       .  33.81\n",
       "1    BER         BOD  AA0048BERBOD             .       .  34.21\n",
       "2    BER         TFB  AA0040BERBOD  AA0104BODTFB       .   0.99\n",
       "3    BER         TFB  AA0040BERBOD  AA0106BODTFB       .   0.67\n",
       "4    BER         TFB  AA0048BERBOD  AA0080BODTFB       .   2.66"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:48:00.094610Z",
     "iopub.status.busy": "2021-06-14T06:48:00.093609Z",
     "iopub.status.idle": "2021-06-14T06:48:00.143613Z",
     "shell.execute_reply": "2021-06-14T06:48:00.141610Z",
     "shell.execute_reply.started": "2021-06-14T06:48:00.094610Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>TRD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>727</th>\n",
       "      <td>DEW</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0061DEWBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>11.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>729</th>\n",
       "      <td>DEW</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0061DEWBOD</td>\n",
       "      <td>AA0090BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>3.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>730</th>\n",
       "      <td>DEW</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0061DEWBOD</td>\n",
       "      <td>AA0094BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>731</th>\n",
       "      <td>DEW</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0061DEWBOD</td>\n",
       "      <td>AA0098BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1523</th>\n",
       "      <td>KOV</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0061KOVBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>4.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1527</th>\n",
       "      <td>KOV</td>\n",
       "      <td>DEW</td>\n",
       "      <td>AA0061KOVDEW</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>1.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1577</th>\n",
       "      <td>KOV</td>\n",
       "      <td>ZBL</td>\n",
       "      <td>AA0061KOVZBL</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>19.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3207</th>\n",
       "      <td>TFB</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0061TFBBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3208</th>\n",
       "      <td>TFB</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0061TFBKOV</td>\n",
       "      <td>AA0065KOVBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>2.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3277</th>\n",
       "      <td>TFB</td>\n",
       "      <td>DEW</td>\n",
       "      <td>AA0061TFBDEW</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>11.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3308</th>\n",
       "      <td>TFB</td>\n",
       "      <td>GBJ</td>\n",
       "      <td>AA0061TFBKOV</td>\n",
       "      <td>AA0065KOVBOD</td>\n",
       "      <td>AA0189BODGBJ</td>\n",
       "      <td>0.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3366</th>\n",
       "      <td>TFB</td>\n",
       "      <td>KOV</td>\n",
       "      <td>AA0061TFBKOV</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>50.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3511</th>\n",
       "      <td>TFB</td>\n",
       "      <td>QTH</td>\n",
       "      <td>AA0061TFBKOV</td>\n",
       "      <td>AA0064KOVQTH</td>\n",
       "      <td>.</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3557</th>\n",
       "      <td>TFB</td>\n",
       "      <td>TJU</td>\n",
       "      <td>AA0061TFBKOV</td>\n",
       "      <td>AA0062KOVTJU</td>\n",
       "      <td>.</td>\n",
       "      <td>5.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3620</th>\n",
       "      <td>TFB</td>\n",
       "      <td>ZBL</td>\n",
       "      <td>AA0061TFBZBL</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>5.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4067</th>\n",
       "      <td>ZBL</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0061ZBLBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>12.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4069</th>\n",
       "      <td>ZBL</td>\n",
       "      <td>DEW</td>\n",
       "      <td>AA0061ZBLDEW</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4071</th>\n",
       "      <td>ZBL</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0061ZBLBOD</td>\n",
       "      <td>AA0090BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>1.66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Origin Destination       Flight1       Flight2       Flight3    TRD\n",
       "727     DEW         BOD  AA0061DEWBOD             .             .  11.00\n",
       "729     DEW         TFB  AA0061DEWBOD  AA0090BODTFB             .   3.01\n",
       "730     DEW         TFB  AA0061DEWBOD  AA0094BODTFB             .   1.00\n",
       "731     DEW         TFB  AA0061DEWBOD  AA0098BODTFB             .   0.33\n",
       "1523    KOV         BOD  AA0061KOVBOD             .             .   4.32\n",
       "1527    KOV         DEW  AA0061KOVDEW             .             .   1.65\n",
       "1577    KOV         ZBL  AA0061KOVZBL             .             .  19.30\n",
       "3207    TFB         BOD  AA0061TFBBOD             .             .   0.49\n",
       "3208    TFB         BOD  AA0061TFBKOV  AA0065KOVBOD             .   2.78\n",
       "3277    TFB         DEW  AA0061TFBDEW             .             .  11.18\n",
       "3308    TFB         GBJ  AA0061TFBKOV  AA0065KOVBOD  AA0189BODGBJ   0.61\n",
       "3366    TFB         KOV  AA0061TFBKOV             .             .  50.44\n",
       "3511    TFB         QTH  AA0061TFBKOV  AA0064KOVQTH             .   0.49\n",
       "3557    TFB         TJU  AA0061TFBKOV  AA0062KOVTJU             .   5.26\n",
       "3620    TFB         ZBL  AA0061TFBZBL             .             .   5.64\n",
       "4067    ZBL         BOD  AA0061ZBLBOD             .             .  12.33\n",
       "4069    ZBL         DEW  AA0061ZBLDEW             .             .   0.00\n",
       "4071    ZBL         TFB  AA0061ZBLBOD  AA0090BODTFB             .   1.66"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product[product.Flight1.str.contains('AA0061')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T06:06:45.759385Z",
     "iopub.status.busy": "2021-06-14T06:06:45.759385Z",
     "iopub.status.idle": "2021-06-14T06:06:45.798643Z",
     "shell.execute_reply": "2021-06-14T06:06:45.798643Z",
     "shell.execute_reply.started": "2021-06-14T06:06:45.759385Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    4290.000000\n",
       "mean       19.120205\n",
       "std        41.338939\n",
       "min         0.000000\n",
       "25%         0.960000\n",
       "50%         2.660000\n",
       "75%         8.797500\n",
       "max       263.110000\n",
       "Name: TRD, dtype: float64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product.TRD.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:15:31.226130Z",
     "iopub.status.busy": "2021-06-14T07:15:31.225128Z",
     "iopub.status.idle": "2021-06-14T07:15:31.247127Z",
     "shell.execute_reply": "2021-06-14T07:15:31.246124Z",
     "shell.execute_reply.started": "2021-06-14T07:15:31.226130Z"
    }
   },
   "outputs": [],
   "source": [
    "def explode(x):\n",
    "    try:\n",
    "        ori_des_ls = []\n",
    "        if x == '.':\n",
    "            raise ValueError()\n",
    "        flight = x[:6]\n",
    "        init_ori = x[6:9]\n",
    "        current_ori =x[6:9]\n",
    "        goal_des = x[9:]\n",
    "        current_des = fli_dict[flight].get(current_ori)\n",
    "    except:\n",
    "        pass\n",
    "    else:\n",
    "        while True :\n",
    "            ori_des_ls.append(flight+current_ori+current_des)\n",
    "            if current_des == goal_des:\n",
    "                break\n",
    "            else:\n",
    "                current_ori = current_des\n",
    "                current_des = fli_dict[flight].get(current_ori)\n",
    "    return ori_des_ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:15:38.118659Z",
     "iopub.status.busy": "2021-06-14T07:15:38.117659Z",
     "iopub.status.idle": "2021-06-14T07:15:38.177179Z",
     "shell.execute_reply": "2021-06-14T07:15:38.174181Z",
     "shell.execute_reply.started": "2021-06-14T07:15:38.118659Z"
    }
   },
   "outputs": [],
   "source": [
    "product['Flight1_explod'] = product.Flight1.apply(explode)\n",
    "product['Flight2_explod'] = product.Flight2.apply(explode)\n",
    "product['Flight3_explod'] = product.Flight3.apply(explode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:15:44.874081Z",
     "iopub.status.busy": "2021-06-14T07:15:44.873080Z",
     "iopub.status.idle": "2021-06-14T07:15:44.933067Z",
     "shell.execute_reply": "2021-06-14T07:15:44.928061Z",
     "shell.execute_reply.started": "2021-06-14T07:15:44.874081Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>TRD</th>\n",
       "      <th>Flight1_explod</th>\n",
       "      <th>Flight2_explod</th>\n",
       "      <th>Flight3_explod</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>33.81</td>\n",
       "      <td>[AA0040BERBOD]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>34.21</td>\n",
       "      <td>[AA0048BERBOD]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>0.99</td>\n",
       "      <td>[AA0040BERBOD]</td>\n",
       "      <td>[AA0104BODTFB]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0106BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>0.67</td>\n",
       "      <td>[AA0040BERBOD]</td>\n",
       "      <td>[AA0106BODTFB]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>AA0080BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>2.66</td>\n",
       "      <td>[AA0048BERBOD]</td>\n",
       "      <td>[AA0080BODTFB]</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1       Flight2 Flight3    TRD  \\\n",
       "0    BER         BOD  AA0040BERBOD             .       .  33.81   \n",
       "1    BER         BOD  AA0048BERBOD             .       .  34.21   \n",
       "2    BER         TFB  AA0040BERBOD  AA0104BODTFB       .   0.99   \n",
       "3    BER         TFB  AA0040BERBOD  AA0106BODTFB       .   0.67   \n",
       "4    BER         TFB  AA0048BERBOD  AA0080BODTFB       .   2.66   \n",
       "\n",
       "   Flight1_explod  Flight2_explod Flight3_explod  \n",
       "0  [AA0040BERBOD]              []             []  \n",
       "1  [AA0048BERBOD]              []             []  \n",
       "2  [AA0040BERBOD]  [AA0104BODTFB]             []  \n",
       "3  [AA0040BERBOD]  [AA0106BODTFB]             []  \n",
       "4  [AA0048BERBOD]  [AA0080BODTFB]             []  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:19:25.768557Z",
     "iopub.status.busy": "2021-06-14T07:19:25.766557Z",
     "iopub.status.idle": "2021-06-14T07:19:26.117675Z",
     "shell.execute_reply": "2021-06-14T07:19:26.115676Z",
     "shell.execute_reply.started": "2021-06-14T07:19:25.767558Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>TRD</th>\n",
       "      <th>Flight1_explod</th>\n",
       "      <th>Flight2_explod</th>\n",
       "      <th>Flight3_explod</th>\n",
       "      <th>TF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>33.81</td>\n",
       "      <td>[AA0040BERBOD]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[AA0040BERBOD]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>34.21</td>\n",
       "      <td>[AA0048BERBOD]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[AA0048BERBOD]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>0.99</td>\n",
       "      <td>[AA0040BERBOD]</td>\n",
       "      <td>[AA0104BODTFB]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[AA0040BERBOD, AA0104BODTFB]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0106BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>0.67</td>\n",
       "      <td>[AA0040BERBOD]</td>\n",
       "      <td>[AA0106BODTFB]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[AA0040BERBOD, AA0106BODTFB]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>AA0080BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>2.66</td>\n",
       "      <td>[AA0048BERBOD]</td>\n",
       "      <td>[AA0080BODTFB]</td>\n",
       "      <td>[]</td>\n",
       "      <td>[AA0048BERBOD, AA0080BODTFB]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1       Flight2 Flight3    TRD  \\\n",
       "0    BER         BOD  AA0040BERBOD             .       .  33.81   \n",
       "1    BER         BOD  AA0048BERBOD             .       .  34.21   \n",
       "2    BER         TFB  AA0040BERBOD  AA0104BODTFB       .   0.99   \n",
       "3    BER         TFB  AA0040BERBOD  AA0106BODTFB       .   0.67   \n",
       "4    BER         TFB  AA0048BERBOD  AA0080BODTFB       .   2.66   \n",
       "\n",
       "   Flight1_explod  Flight2_explod Flight3_explod                            TF  \n",
       "0  [AA0040BERBOD]              []             []                [AA0040BERBOD]  \n",
       "1  [AA0048BERBOD]              []             []                [AA0048BERBOD]  \n",
       "2  [AA0040BERBOD]  [AA0104BODTFB]             []  [AA0040BERBOD, AA0104BODTFB]  \n",
       "3  [AA0040BERBOD]  [AA0106BODTFB]             []  [AA0040BERBOD, AA0106BODTFB]  \n",
       "4  [AA0048BERBOD]  [AA0080BODTFB]             []  [AA0048BERBOD, AA0080BODTFB]  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product['TF'] = product.Flight1_explod + product.Flight2_explod + product.Flight3_explod\n",
    "product.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:20:35.602503Z",
     "iopub.status.busy": "2021-06-14T07:20:35.602503Z",
     "iopub.status.idle": "2021-06-14T07:20:35.616848Z",
     "shell.execute_reply": "2021-06-14T07:20:35.614851Z",
     "shell.execute_reply.started": "2021-06-14T07:20:35.602503Z"
    }
   },
   "outputs": [],
   "source": [
    "product.drop(product.columns[product.columns.str.contains('explod')],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:20:42.514113Z",
     "iopub.status.busy": "2021-06-14T07:20:42.513113Z",
     "iopub.status.idle": "2021-06-14T07:20:42.560116Z",
     "shell.execute_reply": "2021-06-14T07:20:42.557117Z",
     "shell.execute_reply.started": "2021-06-14T07:20:42.514113Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Flight1</th>\n",
       "      <th>Flight2</th>\n",
       "      <th>Flight3</th>\n",
       "      <th>TRD</th>\n",
       "      <th>TF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>33.81</td>\n",
       "      <td>[AA0040BERBOD]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BER</td>\n",
       "      <td>BOD</td>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>.</td>\n",
       "      <td>.</td>\n",
       "      <td>34.21</td>\n",
       "      <td>[AA0048BERBOD]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0104BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>0.99</td>\n",
       "      <td>[AA0040BERBOD, AA0104BODTFB]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0040BERBOD</td>\n",
       "      <td>AA0106BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>0.67</td>\n",
       "      <td>[AA0040BERBOD, AA0106BODTFB]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BER</td>\n",
       "      <td>TFB</td>\n",
       "      <td>AA0048BERBOD</td>\n",
       "      <td>AA0080BODTFB</td>\n",
       "      <td>.</td>\n",
       "      <td>2.66</td>\n",
       "      <td>[AA0048BERBOD, AA0080BODTFB]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Origin Destination       Flight1       Flight2 Flight3    TRD  \\\n",
       "0    BER         BOD  AA0040BERBOD             .       .  33.81   \n",
       "1    BER         BOD  AA0048BERBOD             .       .  34.21   \n",
       "2    BER         TFB  AA0040BERBOD  AA0104BODTFB       .   0.99   \n",
       "3    BER         TFB  AA0040BERBOD  AA0106BODTFB       .   0.67   \n",
       "4    BER         TFB  AA0048BERBOD  AA0080BODTFB       .   2.66   \n",
       "\n",
       "                             TF  \n",
       "0                [AA0040BERBOD]  \n",
       "1                [AA0048BERBOD]  \n",
       "2  [AA0040BERBOD, AA0104BODTFB]  \n",
       "3  [AA0040BERBOD, AA0106BODTFB]  \n",
       "4  [AA0048BERBOD, AA0080BODTFB]  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "product.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:26:25.565383Z",
     "iopub.status.busy": "2021-06-14T07:26:25.563384Z",
     "iopub.status.idle": "2021-06-14T07:26:25.584384Z",
     "shell.execute_reply": "2021-06-14T07:26:25.581383Z",
     "shell.execute_reply.started": "2021-06-14T07:26:25.565383Z"
    }
   },
   "outputs": [],
   "source": [
    "s = [1,2,3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:27:55.572703Z",
     "iopub.status.busy": "2021-06-14T07:27:55.571701Z",
     "iopub.status.idle": "2021-06-14T07:27:55.596705Z",
     "shell.execute_reply": "2021-06-14T07:27:55.593701Z",
     "shell.execute_reply.started": "2021-06-14T07:27:55.572703Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "['AA0040BERBOD', 'AA0104BODTFB'].index('AA0104BODTFB')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:21:07.668591Z",
     "iopub.status.busy": "2021-06-14T07:21:07.667592Z",
     "iopub.status.idle": "2021-06-14T07:21:07.702594Z",
     "shell.execute_reply": "2021-06-14T07:21:07.699594Z",
     "shell.execute_reply.started": "2021-06-14T07:21:07.668591Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>dep_ucl</th>\n",
       "      <th>arr_ucl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0001</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021/5/29 7:55</td>\n",
       "      <td>2021/5/29 10:38</td>\n",
       "      <td>2021/5/29 11:55</td>\n",
       "      <td>2021/5/29 17:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0002</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021/5/29 14:05</td>\n",
       "      <td>2021/5/29 21:59</td>\n",
       "      <td>2021/5/29 21:05</td>\n",
       "      <td>2021/5/30 1:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0003</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021/5/29 17:40</td>\n",
       "      <td>2021/5/29 20:25</td>\n",
       "      <td>2021/5/29 21:40</td>\n",
       "      <td>2021/5/30 3:25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0004</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021/5/29 8:40</td>\n",
       "      <td>2021/5/29 16:40</td>\n",
       "      <td>2021/5/29 15:40</td>\n",
       "      <td>2021/5/29 20:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0005</td>\n",
       "      <td>EDB</td>\n",
       "      <td>MBY</td>\n",
       "      <td>2021/5/29 9:10</td>\n",
       "      <td>2021/5/29 12:02</td>\n",
       "      <td>2021/5/29 13:10</td>\n",
       "      <td>2021/5/29 19:02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   flight origin destination          deptime          arrtime  \\\n",
       "0  AA0001    EDB         TFB   2021/5/29 7:55  2021/5/29 10:38   \n",
       "1  AA0002    TFB         EDB  2021/5/29 14:05  2021/5/29 21:59   \n",
       "2  AA0003    EDB         TFB  2021/5/29 17:40  2021/5/29 20:25   \n",
       "3  AA0004    TFB         EDB   2021/5/29 8:40  2021/5/29 16:40   \n",
       "4  AA0005    EDB         MBY   2021/5/29 9:10  2021/5/29 12:02   \n",
       "\n",
       "           dep_ucl          arr_ucl  \n",
       "0  2021/5/29 11:55  2021/5/29 17:38  \n",
       "1  2021/5/29 21:05   2021/5/30 1:59  \n",
       "2  2021/5/29 21:40   2021/5/30 3:25  \n",
       "3  2021/5/29 15:40  2021/5/29 20:40  \n",
       "4  2021/5/29 13:10  2021/5/29 19:02  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_sche.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:25:15.572070Z",
     "iopub.status.busy": "2021-06-14T07:25:15.571071Z",
     "iopub.status.idle": "2021-06-14T07:25:15.616092Z",
     "shell.execute_reply": "2021-06-14T07:25:15.614091Z",
     "shell.execute_reply.started": "2021-06-14T07:25:15.572070Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>dep_ucl</th>\n",
       "      <th>arr_ucl</th>\n",
       "      <th>Flight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0001</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021/5/29 7:55</td>\n",
       "      <td>2021/5/29 10:38</td>\n",
       "      <td>2021/5/29 11:55</td>\n",
       "      <td>2021/5/29 17:38</td>\n",
       "      <td>AA0001EDBTFB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0002</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021/5/29 14:05</td>\n",
       "      <td>2021/5/29 21:59</td>\n",
       "      <td>2021/5/29 21:05</td>\n",
       "      <td>2021/5/30 1:59</td>\n",
       "      <td>AA0002TFBEDB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0003</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021/5/29 17:40</td>\n",
       "      <td>2021/5/29 20:25</td>\n",
       "      <td>2021/5/29 21:40</td>\n",
       "      <td>2021/5/30 3:25</td>\n",
       "      <td>AA0003EDBTFB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0004</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021/5/29 8:40</td>\n",
       "      <td>2021/5/29 16:40</td>\n",
       "      <td>2021/5/29 15:40</td>\n",
       "      <td>2021/5/29 20:40</td>\n",
       "      <td>AA0004TFBEDB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0005</td>\n",
       "      <td>EDB</td>\n",
       "      <td>MBY</td>\n",
       "      <td>2021/5/29 9:10</td>\n",
       "      <td>2021/5/29 12:02</td>\n",
       "      <td>2021/5/29 13:10</td>\n",
       "      <td>2021/5/29 19:02</td>\n",
       "      <td>AA0005EDBMBY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   flight origin destination          deptime          arrtime  \\\n",
       "0  AA0001    EDB         TFB   2021/5/29 7:55  2021/5/29 10:38   \n",
       "1  AA0002    TFB         EDB  2021/5/29 14:05  2021/5/29 21:59   \n",
       "2  AA0003    EDB         TFB  2021/5/29 17:40  2021/5/29 20:25   \n",
       "3  AA0004    TFB         EDB   2021/5/29 8:40  2021/5/29 16:40   \n",
       "4  AA0005    EDB         MBY   2021/5/29 9:10  2021/5/29 12:02   \n",
       "\n",
       "           dep_ucl          arr_ucl        Flight  \n",
       "0  2021/5/29 11:55  2021/5/29 17:38  AA0001EDBTFB  \n",
       "1  2021/5/29 21:05   2021/5/30 1:59  AA0002TFBEDB  \n",
       "2  2021/5/29 21:40   2021/5/30 3:25  AA0003EDBTFB  \n",
       "3  2021/5/29 15:40  2021/5/29 20:40  AA0004TFBEDB  \n",
       "4  2021/5/29 13:10  2021/5/29 19:02  AA0005EDBMBY  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_sche['Flight'] = time_sche.flight + time_sche.origin + time_sche.destination\n",
    "time_sche.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:30:57.557017Z",
     "iopub.status.busy": "2021-06-14T07:30:57.556017Z",
     "iopub.status.idle": "2021-06-14T07:30:57.565014Z",
     "shell.execute_reply": "2021-06-14T07:30:57.563013Z",
     "shell.execute_reply.started": "2021-06-14T07:30:57.556017Z"
    }
   },
   "outputs": [],
   "source": [
    "def isinlist(Flight,x):\n",
    "    try:\n",
    "        Flight.index(x)\n",
    "    except:\n",
    "        return False\n",
    "    else:\n",
    "        return True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:40:12.864290Z",
     "iopub.status.busy": "2021-06-14T07:40:12.864290Z",
     "iopub.status.idle": "2021-06-14T07:40:12.870293Z",
     "shell.execute_reply": "2021-06-14T07:40:12.869289Z",
     "shell.execute_reply.started": "2021-06-14T07:40:12.864290Z"
    }
   },
   "outputs": [],
   "source": [
    "def calc_total_RD(Flight):\n",
    "    return (product.TRD *product.TF.apply(isinlist,x=Flight)).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:41:09.487833Z",
     "iopub.status.busy": "2021-06-14T07:41:09.486824Z",
     "iopub.status.idle": "2021-06-14T07:41:16.993376Z",
     "shell.execute_reply": "2021-06-14T07:41:16.991379Z",
     "shell.execute_reply.started": "2021-06-14T07:41:09.487833Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>dep_ucl</th>\n",
       "      <th>arr_ucl</th>\n",
       "      <th>Flight</th>\n",
       "      <th>TRD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0001</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021/5/29 7:55</td>\n",
       "      <td>2021/5/29 10:38</td>\n",
       "      <td>2021/5/29 11:55</td>\n",
       "      <td>2021/5/29 17:38</td>\n",
       "      <td>AA0001EDBTFB</td>\n",
       "      <td>235.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0002</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021/5/29 14:05</td>\n",
       "      <td>2021/5/29 21:59</td>\n",
       "      <td>2021/5/29 21:05</td>\n",
       "      <td>2021/5/30 1:59</td>\n",
       "      <td>AA0002TFBEDB</td>\n",
       "      <td>197.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0003</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021/5/29 17:40</td>\n",
       "      <td>2021/5/29 20:25</td>\n",
       "      <td>2021/5/29 21:40</td>\n",
       "      <td>2021/5/30 3:25</td>\n",
       "      <td>AA0003EDBTFB</td>\n",
       "      <td>146.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0004</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021/5/29 8:40</td>\n",
       "      <td>2021/5/29 16:40</td>\n",
       "      <td>2021/5/29 15:40</td>\n",
       "      <td>2021/5/29 20:40</td>\n",
       "      <td>AA0004TFBEDB</td>\n",
       "      <td>245.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0005</td>\n",
       "      <td>EDB</td>\n",
       "      <td>MBY</td>\n",
       "      <td>2021/5/29 9:10</td>\n",
       "      <td>2021/5/29 12:02</td>\n",
       "      <td>2021/5/29 13:10</td>\n",
       "      <td>2021/5/29 19:02</td>\n",
       "      <td>AA0005EDBMBY</td>\n",
       "      <td>197.51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   flight origin destination          deptime          arrtime  \\\n",
       "0  AA0001    EDB         TFB   2021/5/29 7:55  2021/5/29 10:38   \n",
       "1  AA0002    TFB         EDB  2021/5/29 14:05  2021/5/29 21:59   \n",
       "2  AA0003    EDB         TFB  2021/5/29 17:40  2021/5/29 20:25   \n",
       "3  AA0004    TFB         EDB   2021/5/29 8:40  2021/5/29 16:40   \n",
       "4  AA0005    EDB         MBY   2021/5/29 9:10  2021/5/29 12:02   \n",
       "\n",
       "           dep_ucl          arr_ucl        Flight     TRD  \n",
       "0  2021/5/29 11:55  2021/5/29 17:38  AA0001EDBTFB  235.30  \n",
       "1  2021/5/29 21:05   2021/5/30 1:59  AA0002TFBEDB  197.61  \n",
       "2  2021/5/29 21:40   2021/5/30 3:25  AA0003EDBTFB  146.05  \n",
       "3  2021/5/29 15:40  2021/5/29 20:40  AA0004TFBEDB  245.98  \n",
       "4  2021/5/29 13:10  2021/5/29 19:02  AA0005EDBMBY  197.51  "
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_sche['TRD'] = time_sche.Flight.apply(calc_total_RD)\n",
    "time_sche.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:41:47.615924Z",
     "iopub.status.busy": "2021-06-14T07:41:47.614924Z",
     "iopub.status.idle": "2021-06-14T07:41:47.653934Z",
     "shell.execute_reply": "2021-06-14T07:41:47.649928Z",
     "shell.execute_reply.started": "2021-06-14T07:41:47.615924Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>flight</th>\n",
       "      <th>origin</th>\n",
       "      <th>destination</th>\n",
       "      <th>deptime</th>\n",
       "      <th>arrtime</th>\n",
       "      <th>dep_ucl</th>\n",
       "      <th>arr_ucl</th>\n",
       "      <th>TRD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA0001</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021/5/29 7:55</td>\n",
       "      <td>2021/5/29 10:38</td>\n",
       "      <td>2021/5/29 11:55</td>\n",
       "      <td>2021/5/29 17:38</td>\n",
       "      <td>235.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA0002</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021/5/29 14:05</td>\n",
       "      <td>2021/5/29 21:59</td>\n",
       "      <td>2021/5/29 21:05</td>\n",
       "      <td>2021/5/30 1:59</td>\n",
       "      <td>197.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA0003</td>\n",
       "      <td>EDB</td>\n",
       "      <td>TFB</td>\n",
       "      <td>2021/5/29 17:40</td>\n",
       "      <td>2021/5/29 20:25</td>\n",
       "      <td>2021/5/29 21:40</td>\n",
       "      <td>2021/5/30 3:25</td>\n",
       "      <td>146.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA0004</td>\n",
       "      <td>TFB</td>\n",
       "      <td>EDB</td>\n",
       "      <td>2021/5/29 8:40</td>\n",
       "      <td>2021/5/29 16:40</td>\n",
       "      <td>2021/5/29 15:40</td>\n",
       "      <td>2021/5/29 20:40</td>\n",
       "      <td>245.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA0005</td>\n",
       "      <td>EDB</td>\n",
       "      <td>MBY</td>\n",
       "      <td>2021/5/29 9:10</td>\n",
       "      <td>2021/5/29 12:02</td>\n",
       "      <td>2021/5/29 13:10</td>\n",
       "      <td>2021/5/29 19:02</td>\n",
       "      <td>197.51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   flight origin destination          deptime          arrtime  \\\n",
       "0  AA0001    EDB         TFB   2021/5/29 7:55  2021/5/29 10:38   \n",
       "1  AA0002    TFB         EDB  2021/5/29 14:05  2021/5/29 21:59   \n",
       "2  AA0003    EDB         TFB  2021/5/29 17:40  2021/5/29 20:25   \n",
       "3  AA0004    TFB         EDB   2021/5/29 8:40  2021/5/29 16:40   \n",
       "4  AA0005    EDB         MBY   2021/5/29 9:10  2021/5/29 12:02   \n",
       "\n",
       "           dep_ucl          arr_ucl     TRD  \n",
       "0  2021/5/29 11:55  2021/5/29 17:38  235.30  \n",
       "1  2021/5/29 21:05   2021/5/30 1:59  197.61  \n",
       "2  2021/5/29 21:40   2021/5/30 3:25  146.05  \n",
       "3  2021/5/29 15:40  2021/5/29 20:40  245.98  \n",
       "4  2021/5/29 13:10  2021/5/29 19:02  197.51  "
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_sche.drop(['Flight'],axis=1,inplace=True)\n",
    "time_sche.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:42:02.723136Z",
     "iopub.status.busy": "2021-06-14T07:42:02.721140Z",
     "iopub.status.idle": "2021-06-14T07:42:02.763139Z",
     "shell.execute_reply": "2021-06-14T07:42:02.760142Z",
     "shell.execute_reply.started": "2021-06-14T07:42:02.723136Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    815.000000\n",
       "mean     116.508564\n",
       "std       61.732865\n",
       "min        7.510000\n",
       "25%       66.625000\n",
       "50%      100.480000\n",
       "75%      165.370000\n",
       "max      320.410000\n",
       "Name: TRD, dtype: float64"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_sche.TRD.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:43:18.118068Z",
     "iopub.status.busy": "2021-06-14T07:43:18.117065Z",
     "iopub.status.idle": "2021-06-14T07:43:18.132046Z",
     "shell.execute_reply": "2021-06-14T07:43:18.129051Z",
     "shell.execute_reply.started": "2021-06-14T07:43:18.118068Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "flight          object\n",
       "origin          object\n",
       "destination     object\n",
       "deptime         object\n",
       "arrtime         object\n",
       "dep_ucl         object\n",
       "arr_ucl         object\n",
       "TRD            float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_sche.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-06-14T07:49:01.049785Z",
     "iopub.status.busy": "2021-06-14T07:49:01.048785Z",
     "iopub.status.idle": "2021-06-14T07:49:01.095837Z",
     "shell.execute_reply": "2021-06-14T07:49:01.094855Z",
     "shell.execute_reply.started": "2021-06-14T07:49:01.049785Z"
    }
   },
   "outputs": [],
   "source": [
    "time_sche.to_csv('time_info.csv',index=None)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
